﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace AutomationTools.Libs
{
    public class XlsReader
    {
        Dictionary<string, string> connStrings = new Dictionary<string, string>();

        FileInfo filePath;
        bool usarCabecalhoComoColuna;
        public XlsReader(string filePath)
            : this(filePath, true)
        {

        }

        public XlsReader(string filePath, bool usarCabecalhoComoColuna)
        {
            this.filePath = new FileInfo(filePath);
            this.usarCabecalhoComoColuna = usarCabecalhoComoColuna;

            this.connStrings.Add(".XLS", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR={1};Imex=1\"");
            this.connStrings.Add(".XLSX", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR={1};Imex=1\"");
        }

        private IDbConnection ObterConnection()
        {
            return new OleDbConnection(string.Format(connStrings[filePath.Extension.ToUpper()], filePath, usarCabecalhoComoColuna ? "Yes" : "No"));
        }

        public DataTable ObterDataSet(string sheet)
        {
            return ObterDataSet().Tables[sheet];
        }

        public DataTable ObterDataSet(int sheetIndex)
        {
            return ObterDataSet().Tables[sheetIndex];
        }

        public DataSet ObterDataSet()
        {
            string strSelect = @"SELECT * FROM [{0}]";
            using (OleDbConnection conn = (OleDbConnection)ObterConnection())
            {

                DataSet ds = new DataSet(filePath.Name);
                OleDbDataAdapter da = new OleDbDataAdapter();
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    throw new Exception(string.Format("Erro ao abrir conexão com o arquivo: '{0}'", filePath), ex);
                }
                var tables = conn.GetSchema("Tables");

                foreach (DataRow item in tables.Rows)
                {
                    string tableName = item["TABLE_NAME"].ToString();

                    var command = conn.CreateCommand();
                    command.CommandText = string.Format(strSelect, tableName);
                    command.CommandType = CommandType.Text;
                    da.SelectCommand = command;

                    da.Fill(ds, tableName);
                }
                return ds;
            }
        }

    }
}
